package com.esm.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.esm.dao.MsgDao;
import com.esm.model.Msg;
import com.esm.utils.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**
 * 
 * @author zyj
 *
 */
public class MsgDaoImpl implements MsgDao{

	@Override
	public boolean addMsgInfo(int uid, String lcontent, int oid, String pushTime, int isReturn, int state) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer("INSERT INTO ems_msg (uid,lcontent,oid,pushtime,isreturn,state)");
		sql.append("  VALUES(?,?,?,?,?,?)");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setInt(1, uid);
			ps.setString(2, lcontent);
			ps.setInt(3, oid);
			ps.setString(4, pushTime);
			ps.setInt(5, isReturn);
			ps.setInt(6, state);
			int i=ps.executeUpdate();
			if(i!=0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public JSONArray findMyMsgInfo(int oid) {
		JSONArray myMsg=new JSONArray();
		StringBuffer sql=new StringBuffer("SELECT * FROM ems_msg");
		sql.append(" WHERE oid=?");
		sql.append(" AND isreturn=0");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setInt(1, oid);
			ResultSet rs=ps.executeQuery();
			int floor=0;
			while(rs.next()){
				floor++;
				JSONObject j=new JSONObject();
				j.put("lid", rs.getInt("lid"));
				j.put("floor",floor);
				j.put("uid", rs.getInt("uid"));
				j.put("pushTime", rs.getString("pushTime"));
				j.put("lcontent", rs.getString("lcontent"));
				j.put("state", rs.getInt("state"));
				myMsg.add(j);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return myMsg;
	}

	@Override
	public boolean addMsglgroup() {
		boolean flag=false;
		StringBuffer sql=new StringBuffer("UPDATE ems_msg");
		sql.append(" SET lgroup=lid");
		sql.append(" WHERE isreturn=0");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			int i=ps.executeUpdate();
			if(i!=0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
		return flag;
	}

	@Override
	public JSONArray findReturnMsgInfo(String lgroup,int uid) {
		JSONArray returnMsgInfo = new JSONArray();
		StringBuffer sql=new StringBuffer("SELECT * FROM ems_msg");
		sql.append(" WHERE oid=?");
		sql.append(" AND isreturn=1");
		sql.append(" AND lgroup=?");
		sql.append(" UNION");
		sql.append(" SELECT * FROM ems_msg");
		sql.append(" WHERE uid=?");
		sql.append(" AND isreturn=1");
		sql.append(" AND lgroup=?");
		sql.append(" ORDER BY pushtime");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setInt(1, uid);
			ps.setString(2, lgroup);
			ps.setInt(3, uid);
			ps.setString(4, lgroup);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				JSONObject j=new JSONObject();
				j.put("lid", rs.getInt("lid"));
				j.put("uid", rs.getInt("uid"));
				j.put("lcontent", rs.getString("lcontent"));
				j.put("oid", rs.getInt("oid"));
				j.put("pushtime", rs.getString("pushtime"));
				returnMsgInfo.add(j);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return returnMsgInfo;
	}

	@Override
	public boolean addReturnMsgInfo(int uid, String lcontent, int oid, String pushTime, int isReturn, int state,
			String lgroup) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer("INSERT INTO ems_msg (uid,lcontent,oid,pushtime,isreturn,state,lgroup)");
		sql.append("  VALUES(?,?,?,?,?,?,?)");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setInt(1, uid);
			ps.setString(2, lcontent);
			ps.setInt(3, oid);
			ps.setString(4, pushTime);
			ps.setInt(5, isReturn);
			ps.setInt(6, state);
			ps.setString(7, lgroup);
			int i=ps.executeUpdate();
			if(i!=0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public JSONArray getMyCallMsgInfo(int uid) {
		JSONArray myCallMsg=new JSONArray();
		StringBuffer sql=new StringBuffer("SELECT * FROM ems_msg");
		sql.append(" WHERE uid=?");
		sql.append(" AND isreturn=0");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setInt(1, uid);
			ResultSet rs=ps.executeQuery();
			int floor=0;
			while(rs.next()){
				floor++;
				JSONObject j=new JSONObject();
				j.put("floor", floor);
				j.put("lid", rs.getInt("lid"));
				j.put("lcontent", rs.getString("lcontent"));
				j.put("pushtime", rs.getString("pushtime"));
				j.put("lgroup", rs.getString("lgroup"));
				myCallMsg.add(j);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return myCallMsg;
	}

	@Override
	public JSONArray getMyCallMsgRe(String lgroup) {
		JSONArray callMsgRe=new JSONArray();
		StringBuffer sql=new StringBuffer("SELECT *  FROM ems_msg");
		sql.append(" WHERE lgroup=?");
		sql.append(" AND isreturn=1");
		sql.append(" ORDER BY pushtime");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setString(1, lgroup);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				JSONObject j=new JSONObject();
				j.put("uid", rs.getInt("uid"));
				j.put("lcontent", rs.getString("lcontent"));
				j.put("pushtime", rs.getString("pushtime"));
				callMsgRe.add(j);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return callMsgRe;
	}

}
